package com.young.common.core.excel;

import com.young.common.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel导入实体类
 * 基于poi
 * 该实体类认为在一个excel文件中,待导入的数据是存放在一个sheet页的
 * Created by rookie on 2017/8/17.
 */
public class ExcelImport implements IExcelImport {

    Logger logger = LoggerFactory.getLogger(ExcelImport.class);

    public static final int MAX_NUM_DECIMALS = 10;//单元格最大小数位,默认为最大保留小数点后8位

    private Workbook workbook;//工作簿

    private Sheet[] sheets;//工作表数组,对应excel表格中的多个sheet页

    private Sheet coreSheet;//核心sheet,记录导入数据的sheet页

    public ExcelImport(){
        super();
    }

    public ExcelImport(String fileName, InputStream is){
        this(fileName, is, 0);//默认第一页为数据导入页
    }

    public ExcelImport(MultipartFile file) throws IOException{
        this(file.getOriginalFilename(), file.getInputStream(), 0);//默认第一页为数据导入页
    }

    public ExcelImport(String fileName, InputStream is, int dataSheetIndex){
        if (is != null && fileName != null){
            //初始化工作薄
            try{
                if(fileName.toLowerCase().endsWith("xls")){//03版的office生成的后缀是.xls
                    this.workbook = new HSSFWorkbook(is);
                }else if (fileName.toLowerCase().endsWith("xlsx")){//07版的office生成的后缀是.xlsx
                    this.workbook = new XSSFWorkbook(is);
                }else{
                    logger.warn("[Excel导入模型] 初始化失败,文件类型匹配失败,文件名={}", fileName);
                }
            }catch(IOException e){
                logger.warn("[Excel导入模型] 初始化失败,文件流转为工作薄对象发生异常!");
                e.printStackTrace();
            }

            //初始化sheet
            if (this.workbook != null){
                this.sheets = new Sheet[this.workbook.getNumberOfSheets()];//初始化sheet页
                for (int i=0;i<this.workbook.getNumberOfSheets();i++){
                    this.sheets[i] = this.workbook.getSheetAt(i);
                }

                if (this.sheets != null && this.sheets.length > 0){
                    this.coreSheet = this.sheets[dataSheetIndex];//设置数据页索引
                }
            }else{
                logger.info("[Excel导入模型] 工作薄为空!");
            }
        }else {
            logger.warn("[Excel导入模型] 初始化失败: 文件名或文件流为空!");
        }
    }

    /**
     * 返回对应索引的sheet页,当excel为空或索引超出范围时返回null
     * @param sheetIndex sheet页的索引
     * @return
     */
    @Override
    public Sheet getSheetAt(int sheetIndex) {
        //当excel不为空且索引没有超出范围,返回对应sheet
        if(!isEmpty() && sheetIndex <= getSheetNum()){
            return sheets[sheetIndex];
        }
        return null;
    }

    @Override
    public Sheet[] getAllSheet() {
        return sheets;
    }

    @Override
    public List<Map<String, Object>> getAllData() {
        if (!isEmpty()){
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            for (int i=0;i<this.sheets.length;i++){
                Sheet sheet = this.sheets[i];
                //获取本sheet页的数据
                List<Map<String, Object>> l = this.getDataBySheet(sheet);
                list.addAll(l);//合并到总的list
            }
            return list;
        }
        return null;
    }

    /**
     * 1.默认第一行为标题行;
     * 2.默认标题和数据是相同的列起始;
     * 3.默认从标题行的下一行开始为待导入的数据
     * 4.*每行的数据读取都不会超过标题行的列数,当数据超出标题行时,会被忽略
     * @param sheet
     * @return
     */
    @Override
    public List<Map<String, Object>> getDataBySheet(Sheet sheet) {
        if (sheet != null){
            int headIdx = 0;//标题行的索引,默认第一行为标题行
            int startCellIdx = 0;//起始列索引,默认标题和数据是相同的列
            int startRowIdx = headIdx + 1;//待导入数据起始行,默认从标题行的下一行开始为待导入的数据
            int endRowIdx = sheet.getLastRowNum();//结束行的索引
            //先找出该sheet的标题行
            List<String> header = new ArrayList<String>();
            Row head = sheet.getRow(headIdx);
            int endCellIdx = head.getLastCellNum() - 1;//标题的最后一列索引,getLastCellNum()返回的是列数
            for(int i=headIdx;i<=endCellIdx;i++){
                header.add( this.getCellValue(head.getCell(i))+"" );
            }

            //获取数据
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            for (int i=startRowIdx;i<=endRowIdx;i++){
                Row row = sheet.getRow(i);//行对象
                Map<String,Object> map = new HashMap<String, Object>();//行对象
                boolean flag = false;//标识行是否有效
                //从列起始索引一直读到末尾或标题行末尾列索引处
                int colIdx = row.getLastCellNum() - 1;//最后一列索引
                for(int j=startCellIdx;j<=colIdx;j++){
                    if (j > endCellIdx){
                        logger.info("[Excel导入模型] 单元格超出范围,忽略此单元格,该单元格位于{}行,{}列,值为{}", i+1, j+1, this.getCellValue(row.getCell(j)));
                    }else{
                        Object obj = this.getCellValue(row.getCell(j));
                        //只要一行中有一个单元格是有效的,则该行有效
                        if (obj != null && !"".equals(obj.toString().trim())){
                            flag = true;
                        }
                        map.put(header.get(j), obj);//标题为key,值为value
                    }
                }
                if (flag){//当数据行有效时才装入集合
                    list.add(map);
                }
            }
            return list;
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> getDataBySheetNum(int sheetIndex) {
        Sheet sheet = this.getSheetAt(sheetIndex);
        if (sheet != null){
            return getDataBySheet(sheet);
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> getImportData() {
        return getDataBySheet(coreSheet);
    }

    @Override
    public <T> List<T> getImportDataAsBean(Class<T> cls) throws NoSuchMethodException{
        Sheet sheet = coreSheet;//待导出sheet页
        int headIdx = 0;//标题行的索引,默认第一行为标题行
        int startCellIdx = 0;//起始列索引,默认标题和数据是相同的列
        int startRowIdx = headIdx + 1;//待导入数据起始行,默认从标题行的下一行开始为待导入的数据
        int endRowIdx = sheet.getLastRowNum();//结束行的索引

        //先找出该sheet的标题行
        List<String> header = new ArrayList<String>();
        Row head = sheet.getRow(headIdx);
        int endCellIdx = head.getLastCellNum() - 1;//标题的最后一列索引,getLastCellNum()返回的是列数
        for(int i=headIdx;i<=endCellIdx;i++){
            header.add( this.getCellValue(head.getCell(i))+"" );
        }

        //读取cls中的ExcelColumn注解信息
        Map<String, ExcelColumnUnit> map = new HashMap<String, ExcelColumnUnit>();
        Field[] fields = cls.getDeclaredFields();//所有字段
        for (Field field : fields){
            ExcelColumnUnit excelColumnUnit = new ExcelColumnUnit();
            ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
            if (excelColumn != null){
                excelColumnUnit.setField(field);//字段
                excelColumnUnit.setExcelColumn(excelColumn);//注解
                //字段set方法
                try {
                    excelColumnUnit.setSetMethod(cls.getMethod("set" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1), field.getType()));
                } catch (NoSuchMethodException e) {
                    logger.error("[Excel导入] 字段{}没有对应的public set方法", field.getName());
                    e.printStackTrace();
                    throw e;
                }
                map.put(excelColumn.value(), excelColumnUnit);
            }
        }

        //获取数据
        List<T> list = new ArrayList<T>();
        //注解信息有效时
        if (map != null && !map.isEmpty()){
            for (int i=startRowIdx;i<=endRowIdx;i++){
                Row row = sheet.getRow(i);//行对象
                T item = null;
                try {
                    item = cls.newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                boolean flag = false;//标识行是否有效
                //从列起始索引一直读到末尾或标题行末尾列索引处
                int colIdx = row.getLastCellNum() - 1;//最后一列索引
                for(int j=startCellIdx;j<=colIdx;j++){
                    if (j > endCellIdx){
                        logger.info("[Excel导入] 单元格超出范围,忽略此单元格,该单元格位于{}行,{}列,值为{}", i+1, j+1, this.getCellValue(row.getCell(j)));
                    }else{
                        Object obj = this.getCellValue(row.getCell(j));
                        //只要一行中有一个单元格是有效的,则该行有效
                        if (obj != null && !"".equals(obj.toString().trim())){
                            flag = true;
                        }
                        ExcelColumnUnit unit = map.get(header.get(j));
                        if (unit != null){
                            Field field = unit.getField();
                            if (field != null){
                                try {
                                    if (field.getType().isAssignableFrom(String.class)){//字段为String
                                        unit.getSetMethod().invoke(item, obj.toString());
                                    }else  if (field.getType().isAssignableFrom(Integer.class)){//字段为Integer
                                        if (StringUtils.isNotBlank(obj.toString())){
                                            unit.getSetMethod().invoke(item, Integer.parseInt(obj.toString()));
                                        }
                                    }else  if (field.getType().isAssignableFrom(Float.class)){//字段为Float
                                        if (StringUtils.isNotBlank(obj.toString())){
                                            unit.getSetMethod().invoke(item, Float.parseFloat(obj.toString()));
                                        }
                                    }else  if (field.getType().isAssignableFrom(Long.class)){//字段为Long
                                        if (StringUtils.isNotBlank(obj.toString())){
                                            unit.getSetMethod().invoke(item, Long.parseLong(obj.toString()));
                                        }
                                    }else  if (field.getType().isAssignableFrom(Double.class)){//字段为Double
                                        if (StringUtils.isNotBlank(obj.toString())){
                                            unit.getSetMethod().invoke(item, Double.parseDouble(obj.toString()));
                                        }
                                    }else  if (field.getType().isAssignableFrom(Boolean.class)){//字段为Boolean
                                        if (StringUtils.isNotBlank(obj.toString())){
                                            unit.getSetMethod().invoke(item, Boolean.parseBoolean(obj.toString()));
                                        }
                                    }else  if (field.getType().isAssignableFrom(Date.class)){//字段为Date
                                        if (StringUtils.isNotBlank(obj.toString())){
                                            SimpleDateFormat sdf = null;
                                            if (obj.toString().length() == 10){
                                                sdf = new SimpleDateFormat("yyyy-MM-dd");
                                            }else{
                                                sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                            }
                                            try {
                                                unit.getSetMethod().invoke(item, sdf.parse(obj.toString()));
                                            } catch (ParseException e) {
                                                e.printStackTrace();
                                                logger.error("[Excel导入] 时间格式化异常:{}", obj.toString());
                                            }
                                        }
                                    }else {
                                        logger.warn("[Excel导入] 未知的方法参数类型,方法:{},参数类型:{}", unit.getSetMethod().getName(), field.getType());
                                        unit.getSetMethod().invoke(item, obj);
                                    }
                                } catch (IllegalAccessException e) {
                                    logger.error("[Excel导入] 方法{}执行异常", unit.getSetMethod().getName());
                                    e.printStackTrace();
                                } catch (InvocationTargetException e) {
                                    logger.error("[Excel导入] 方法{}执行异常", unit.getSetMethod().getName());
                                    e.printStackTrace();
                                }
                            }else{
                                logger.warn("[Excel导入] 非空参数出现null,可能被gc回收了");
                            }
                        }else {
                            logger.info("[Excel导入] excel当前列无对应字段信息,列标题为{}", header.get(j));
                        }
                    }
                }
                if (flag){//当数据行有效时才装入集合
                    list.add(item);
                }
            }
        }
        return list;
    }

    @Override
    public boolean isEmpty() {
        //当sheets存在sheet时返回true
        if (getSheetNum() > 0){
            return true;
        }
        return false;
    }

    @Override
    public int getSheetNum() {
        return sheets != null ? sheets.length : 0;
    }

    /**
     * 获取单元格的值
     * 空值返回""
     * @param cell 单元格
     * @return
     */
    public Object getCellValue(Cell cell){
        Object val = "";
        if (cell != null) {
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                // val = cell.getNumericCellValue();
                // 当excel 中的数据为数值或日期是需要特殊处理
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    double d = cell.getNumericCellValue();
                    Date date = HSSFDateUtil.getJavaDate(d);
                    SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    val = dformat.format(date);
                } else {
                    NumberFormat nf = NumberFormat.getInstance();
                    nf.setMaximumFractionDigits(MAX_NUM_DECIMALS);//设置最大小数位数
                    nf.setGroupingUsed(false);// true时的格式：1,234,567,890
                    val = nf.format(cell.getNumericCellValue());// 数值类型的数据为double，所以需要转换一下
                }
            } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                val = cell.getStringCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                val = cell.getCellFormula();
            } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                val = cell.getBooleanCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                val = cell.getErrorCellValue();
            }else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                val = "";
            }else{
                logger.warn("*获取表格数据警告,未知的单元格类型:{}", cell);
            }
        }
        return val;
    }



    public static void main(String[] args){
        /*File file = new File("C:\\Users\\Administrator\\Desktop\\123.xlsx");
        String name = file.getName();
        try {
            IExcelImport ei = new ExcelImport(name, new FileInputStream(file));
            List<TestDO> list = null;
            try {
                list = ei.getImportDataAsBean(TestDO.class);
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            }
            for (TestDO map : list){
                System.out.println("导入excel---文件大小为 " + list.size() + "---" + map);
            }
            ExcelExport ee = new ExcelExport();
            try {
                ee.insertBeanList(list, TestDO.class);
                System.out.println("长度="+ee.getSize());
                try {
                    ee.getWorkbook().write(new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\333.xlsx")));
                } catch (IOException e) {
                    e.printStackTrace();
                }
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }*/
    }
}
